"""
#!/usr/bin/python3
# coding:utf-8            
#
# Copyright (C) 2024 - 2024 Jasonakeke， Inc. All Rights Reserved 
# @Desc	   ：
# @Time    : 2024/8/17 22:40
# @Author  : Code_By_Jasonakeke
# @Email   : 2284037977@qq.com
# @File    : 07-ORM中的多对多关联.py
# @IDE     : PyCharm
"""
from random import random, randint

from sqlalchemy import create_engine, Table, Integer, Column, ForeignKey, String
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

# 数据库的配置变量
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
USERNAME = 'root'
PASSWORD = '123456'
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8mb4'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)

# 创建数据库引擎
engine = create_engine(DB_URI)

# 第一步：创建一个基类（所有 ORM 中的 O，对象模型的超级父类）
Base = declarative_base()

# 学生和课程之间就是多对多关系
# 第一步：定义中间表
temp_tab = Table(
	't_temp_tab',
	Base.metadata,
	# 第三步：定义中间表的两个外键字段作为联合主键
	Column('s_id', Integer, ForeignKey('t_student.id'), primary_key = True),
	Column('c_id', Integer, ForeignKey('t_course.id'), primary_key = True)
)


#  第二步：定义模型类
class Student(Base):
	__tablename__ = 't_student'
	id = Column(Integer, primary_key = True, autoincrement = True)
	name = Column(String(50))
	age = Column(Integer)
	# 第四步：定义关联关系
	course_list = relationship('Course', secondary = temp_tab, backref = 'student_list')

	def __repr__(self):
		return 'Student(id={}, name={}, age={})'.format(self.id, self.name, self.age)

class Course(Base):
	__tablename__ = 't_course'
	id = Column(Integer, primary_key = True, autoincrement = True)
	c_name = Column(String(50))

	def __repr__(self):
		return 'Course(id={}, c_name={})'.format(self.id, self.c_name)

# Base.metadata.create_all(engine)


session = sessionmaker(bind = engine)()


def save():
	students = []
	for x in range(10):
		s = Student(name = 'name-{}'.format(x), age = randint(18, 100))
		students.append(s)
	session.add_all(students)
	session.commit()


def query():
	# 排序：直接在 query 查询的时候加入 order_by 排序
	students = session.query(Student).order_by(Student.age).all()
	students = session.query(Student).order_by(Student.age.desc()).all()
	print(students)

def page_query():
	# offset：偏移量，limit：限制条数
	# students = session.query(Student).offset(2).limit(10).all()
	# slice 从第 n 条后面开始取，一直到第 m 条结束
	students = session.query(Student).slice(2, 8).all()
	print(students)


if __name__ == '__main__':
	page_query()
